﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;

namespace HotelManagement.Class
{
    public class BankAccountStaffDBManager
    {
        public static int insertBankAccountStaff(BankAccountStaff ba)
        {
            int rowsInserted = 0;


            try
            {
                SqlConnection conn = StaffDBManager.ConnectToDatabase();

                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO BankAccountStaff (AccountNumber, AccountName, AccountType, TransitCode, BankName, BankBranch, StaffID)" +
                    " VALUES (@accountNumber, @accountName, @accountType, @transitCode, @bankName, @bankBranch, @staffID)";
                comm.Parameters.AddWithValue("@accountNumber", ba.AccountNumber);
                comm.Parameters.AddWithValue("@accountName", ba.AccountName);
                comm.Parameters.AddWithValue("@accountType", ba.AccountType);
                comm.Parameters.AddWithValue("@transitCode", ba.TransitCode);
                comm.Parameters.AddWithValue("@bankName", ba.BankName);
                comm.Parameters.AddWithValue("@bankBranch", ba.BankBranch);
                comm.Parameters.AddWithValue("@staffID", ba.StaffID);


                rowsInserted = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsInserted;
        }



        public static BankAccountStaff getBankAccountbyStaffId(string id)
        {
            BankAccountStaff ba = null;

            try
            {
                SqlConnection conn = StaffDBManager.ConnectToDatabase();
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM BankAccountStaff WHERE StaffID=@StaffID";
                comm.Parameters.AddWithValue("@StaffID", id);
                SqlDataReader dr = comm.ExecuteReader();
                if (dr.Read())
                {
                    ba = new BankAccountStaff();
                    ba.AccountNumber = (string)dr["AccountNumber"];
                    ba.AccountName = (string)dr["AccountName"];
                    ba.AccountType = (string)dr["AccountType"];
                    ba.TransitCode = (string)dr["TransitCode"];
                    ba.BankName = (string)dr["BankName"];
                    ba.BankBranch = (string)dr["BankBranch"];
                    ba.StaffID = (string)dr["StaffID"];
                }
                dr.Close();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }

            return ba;

        }

        public static int updateBankAccountStaff(BankAccountStaff ba)
        {
            int rowsUpdated = 0;


            try
            {
                SqlConnection conn = StaffDBManager.ConnectToDatabase();
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE BankAccountStaff SET AccountNumber=@accountNumber,AccountName=@accountName,  AccountType=@accountType, TransitCode=@transitCode, BankName=@bankName, BankBranch=@bankBranch, StaffID=@staffID WHERE StaffID=@StaffID";

                comm.Parameters.AddWithValue("@accountNumber", ba.AccountNumber);
                comm.Parameters.AddWithValue("@accountName", ba.AccountName);
                comm.Parameters.AddWithValue("@accountType", ba.AccountType);
                comm.Parameters.AddWithValue("@transitCode", ba.TransitCode);
                comm.Parameters.AddWithValue("@bankName", ba.BankName);
                comm.Parameters.AddWithValue("@bankBranch", ba.BankBranch);
                comm.Parameters.AddWithValue("@staffID", ba.StaffID);


                rowsUpdated = comm.ExecuteNonQuery();
                conn.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsUpdated;
        }
        public static int deleteBankAccountStaff(string staffID)
        {
            int rowsDeleted = 0;

            try
            {
                SqlConnection conn = StaffDBManager.ConnectToDatabase();
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "Delete from BankAccountStaff where StaffID=@staffID";
                comm.Parameters.AddWithValue("@staffID", staffID);
                rowsDeleted = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            return rowsDeleted;
        }
    }
}